/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package DataAccess;

import BussinesLogic.Request;
import DataModel.*;
import java.sql.ResultSet;
import java.util.Date;

public class Dao_Competencia {

    public Dao_Competencia() {}
    
    public Request registrarCompetencia(Competencia competencia){
        
        String sql = "INSERT INTO \"Competencia\" (nombre, disciplina, descripcion, fecha, hora, n_jug_fase1,"
                + "n_jug_fase2,  \"EspacioDeportivo\")"
                + " VALUES('" + competencia.getNombre() + "','" + competencia.getDisciplina().getNombre()
                + "','" + competencia.getDescripcion() + "','" + competencia.getFecha() + "','" + competencia.getHora()
                + "','" + competencia.getCantJugFase1() + "'," + competencia.getCantJugFase2()
                + ",'" + competencia.getEscenario().getNombre() + "')";
        
        Request request = new Request();

        try {
            Connect conn = new Connect();
            driverPGSQL driver = new driverPGSQL(conn);
            request = driver.queryInsert(sql);
            conn.close();
        } catch (Exception e) {
            request.setTransac(false);
            request.setMessage(e.getMessage());
        }
        return request;
    }
    
    public Request consultarCompetencia(String campo, String valor){
        
        Request request = new Request();
        Competencia competencia = new Competencia();
        Disciplina disciplina = new Disciplina();
        EspacioDeportivo ed = new EspacioDeportivo();
        String sql = "SELECT * FROM \"Competencia\" WHERE \"" + campo + "\"='" + valor + "'";
        
        try {
            Connect conn = new Connect();
            driverPGSQL driver = new driverPGSQL(conn);
            request = driver.querySelect(sql);
            ResultSet rqs = request.getRequest();
            while (rqs.next()) {
                competencia.setNombre(rqs.getString(1));
                disciplina.setNombre(rqs.getString(2));
                competencia.setDisciplina(disciplina);
                competencia.setDescripcion(rqs.getString(3));
                competencia.setFecha(rqs.getDate(4));
                competencia.setHora(rqs.getTime(5).toString());
                competencia.setCantJugFase1(rqs.getInt(6));
                competencia.setCantJugFase2(rqs.getInt(7));
                ed.setNombre(rqs.getString(8));
                competencia.setEscenario(ed);
            }
            request.setObject(competencia);
            conn.close();
        } catch (Exception e) {
            request.setTransac(false);
            request.setMessage(e.getMessage());
        }
        
        return request;
    }
    
    public Request consultarCompetencias() {
        
        Request request = new Request();
        String sql = "SELECT nombre FROM \"Competencia\"";        
        
        java.util.Vector<Competencia> v = new java.util.Vector(1,1);
        try {
            Connect conn = new Connect();
            driverPGSQL driver = new driverPGSQL(conn);
            request = driver.querySelect(sql);
            ResultSet rqs = request.getRequest();
            while (rqs.next()) {
                Competencia competencia = new Competencia();
                competencia.setNombre(rqs.getString(1));
                v.add(competencia);
            }
            request.setObject(v);
            conn.close();
        } catch (Exception e) {
            request.setTransac(false);
            request.setMessage(e.getMessage());
        }
        
        return request;
    }
    
    public Request consultarHorarioDisponible(String escenario, Date fecha, String horaInicio, String horaFinal) {

        Request request = new Request();
        String sql = "SELECT count(*) FROM \"Competencia\" WHERE \"EspacioDeportivo\"='" + escenario + "' "
                + "AND fecha='" + fecha + "' AND hora>='" + horaInicio + "' AND hora<='" + horaFinal + "'";
        try {
            Connect conn = new Connect();
            driverPGSQL driver = new driverPGSQL(conn);
            request = driver.querySelect(sql);
            ResultSet rqs = request.getRequest();
            while (rqs.next()) {
                request.setObject(rqs.getInt(1));
            }
            conn.close();
        } catch (Exception e) {
            request.setTransac(false);
            request.setMessage(e.getMessage());
        }
        
        return request;
    }

}
